﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace sqlservice.util
{
    class DBHelper
    {

        private static String str_con = "server=.;database=stu_info;integrated security=SSPI";//连接到数据库
        private static SqlConnection con = new SqlConnection(str_con);

        /// <summary>
        /// 连接数据库
        /// </summary>
        public static String connect() {
            //SqlConnection con = new SqlConnection(str_con);
            //打开连接
            String ret;
            try
            {
                con.Open();

                String state = con.State.ToString();
                String version = con.ServerVersion;
                String database = con.Database;
                String intance = con.DataSource;
                String timeout = con.ConnectionTimeout.ToString();
                String packetSize = con.PacketSize.ToString();
                String workstation = con.WorkstationId;
                ret = "连接状态: " + state
                   + "\n" + "sql server 实例名：" + intance
                   + "\n" + "数据库：" + database
                   + "\n" + "sql server 版本：" + version
                   + "\n" + "数据库客户端id：" + workstation
                   + "\n" + "等待时间：" + timeout
                   + "\n" + "数据包大小：" + packetSize;
                
            }
            catch (Exception e) {

                ret = e.Message;
            }

            return ret;
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public static String  close() {
            //关闭连接
            String ret;
            try
            {
                con.Close();

                ret = "数据库已关闭";
                  
            }
            catch (Exception e)
            {

                ret = e.Message;
            }

            return ret;
        }

        /// <summary>
        /// 往数据库进行增删改操作
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>发生改变了的行</returns>
        public static int insert(String sql)
        {

            int i = 0;
            try
            {
                //操作数据库的工具SqlCommand
                SqlCommand cmd = new SqlCommand(sql, con);//(操作语句和链接工具)
                i = cmd.ExecuteNonQuery();//执行操作返回影响行数（）
            }
            catch (Exception e) {
                MessageBox.Show(e.Message);
            }

            return i;
            
        }


        /// <summary>
        /// 查询数据库中的数据
        /// </summary>
        /// <param name="sql">将要执行的sql语句</param>
        /// <returns></returns>
        public static DataSet query(String sql)
        {
            //储存数据的工具初始化
            DataSet ds = new DataSet();
            //相当于链接数据库的一个工具类（连接字符串）
            try
            {
                //用SqlConnection工具链接数据库，在通过sql查询语句查询结果现存入sql适配器
                SqlDataAdapter sda = new SqlDataAdapter(sql, con);   //(查询语句和连接工具)
                sda.Fill(ds);    //将适配器数据存入DataSet工具中
            }
            catch (Exception e) {
                MessageBox.Show(e.Message);
            }
             
            return ds;
            
        }
    }
}
